package laboratorioV.grupo21.tp4.checkeosPrevios;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class CheckSource {

	public void checkearYgenerarTodo() throws Exception{
		Connection con = null;	
		try {					
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");			
			con = DriverManager.getConnection("jdbc:derby:derbyDb/TP6;create=false");
			
		}
		catch (Exception e){
			System.out.println("CREANDO TABLAS ESENCIALES ....");
			try{
				crearDB();
			}
			catch (Exception ex){
				throw ex;
			}
		}
		finally{
			if (con != null)con.close();
		}
			
	}
	private void crearDB() throws Exception {
		PreparedStatement ps = null;
		java.sql.Statement sta = null;
		Connection con = null;
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			
			con = DriverManager.getConnection("jdbc:derby:derbyDb/TP6;create=true");
			con.setAutoCommit(false);
				  
			sta = con.createStatement(); 
			sta.executeUpdate("CREATE TABLE CLIENTES (ID_CLIENTE VARCHAR(50) NOT NULL PRIMARY KEY, NOMBRE VARCHAR(50), APELLIDO VARCHAR(50))");
		    sta.executeUpdate("CREATE TABLE PRODUCTOS (ID_PRODUCTO INT NOT NULL PRIMARY KEY, ID_CATEGORIA INT NOT NULL, DESCRIPCION VARCHAR(50))");
		    sta.executeUpdate("CREATE TABLE STOCK (DEPOSITO VARCHAR(50), ID_PASILLO INT, LADO VARCHAR(10), FILA INT, COLUMNA INT, ID_PRODUCTO INT, ID_CLIENTE VARCHAR(50), STOCK INT)");
		    sta.executeUpdate("CREATE TABLE DEPOSITO (DEPOSITO VARCHAR(50), ID_PASILLO INT, LADO VARCHAR(10), MAX_FILAS INT, MAX_COLUMNAS INT)");
		    
		    //INSERTA CLIENTES
		    ps = con.prepareStatement("INSERT INTO CLIENTES (ID_CLIENTE,NOMBRE,APELLIDO) VALUES(?,?,?)");
		    
		    ps.setString(1, "0");
		    ps.setString(2, "Dalmiro");
		    ps.setString(3, "Bustos");
		    ps.executeUpdate();
		    ps.clearParameters();
			      
			ps.setString(1, "1");
			ps.setString(2, "Juan Pablo");
			ps.setString(3, "Royo");
			ps.executeUpdate();
			ps.clearParameters();
			
			ps.setString(1, "2");
			ps.setString(2, "Daniel");
			ps.setString(3, "Islas");
			ps.executeUpdate();
			ps.clearParameters();
			      
			ps.setString(1, "3");
			ps.setString(2, "Homero");
			ps.setString(3, "Simpson");
			ps.executeUpdate();
			      
			//INSERTA PRODUCTOS
			ps = con.prepareStatement("INSERT INTO PRODUCTOS (ID_PRODUCTO,ID_CATEGORIA,DESCRIPCION) VALUES(?,?,?)");
			
			ps.setString(1, "0");
			ps.setString(2, "1");
			ps.setString(3, "Teclado Generico");
			ps.executeUpdate();
			ps.clearParameters();
			
			ps.setString(1, "1");
			ps.setString(2, "2");
			ps.setString(3, "Mouse Logitech x4");
			ps.executeUpdate();
			ps.clearParameters();
			      
			ps.setString(1, "2");
			ps.setString(2, "2");
			ps.setString(3, "Mouse Generico");
			ps.executeUpdate();
			ps.clearParameters();
			      
			ps.setString(1, "3");
			ps.setString(2, "3");
			ps.setString(3, "LCD 15 ViewSonic");
			ps.executeUpdate();
			ps.clearParameters();
			      
			ps.setString(1, "4");
			ps.setString(2, "3");
			ps.setString(3, "LCD 22 DELL");
			ps.executeUpdate();
			ps.clearParameters();
			      
			ps.setString(1, "5");
			ps.setString(2, "3");
			ps.setString(3, "LCD 20 DELL");
			ps.executeUpdate();
			ps.clearParameters();
			
			
			//Guarda la estructura del deposito
			for (int i = 1; i <=3 ; i++) {				    			    
				ps = con.prepareStatement("INSERT INTO DEPOSITO (DEPOSITO, ID_PASILLO, LADO, MAX_FILAS, MAX_COLUMNAS) VALUES(?,?,?,?,?)");
				ps.setString(1, "Mercaderia de Multiproposito");
				ps.setInt(2, i);
				ps.setString(3,"D");
				ps.setInt(4,10 );
				ps.setInt(5,10 );
				ps.executeUpdate();
				ps.clearParameters();
				    
				ps.setString(1, "Mercaderia de Multiproposito");
				ps.setInt(2, i);
				ps.setString(3,"I");
				ps.setInt(4,10 );
				ps.setInt(5,10 );
				ps.executeUpdate();				
					
			}	
						
			con.commit(); 	   
			   
			} catch (SQLException e1) {				
				e1.printStackTrace();
				con.rollback();
			}
			finally{
				try {
					if(ps != null)ps.close();
					if(sta != null)sta.close();
					if(con != null)con.close();
				} catch (Exception e) {
					throw e;
				}
					
			}	 		
	}	
}
